import mysql.connector
from mysql.connector import Error

class DBManager:
    def __init__(self, host, database, user, password):
        self.host = host
        self.database = database
        self.user = user
        self.password = password
        self.connection = None
        self.cursor = None

    def connect(self):
        try:
            self.connection = mysql.connector.connect(
                host=self.host,
                database=self.database,
                user=self.user,
                password=self.password,
                buffered =True
            )
            self.cursor = self.connection.cursor()
        except Error as e:
            print(f"Error: '{e}'")

    def close(self):
        if self.connection.is_connected():
            self.cursor.close()
            self.connection.close()
            print("MySQL connection is closed")

    def execute_query(self, query, params=None):
        try:
            if params is None:
                self.cursor.execute(query)
            else:
                self.cursor.execute(query, params)
            self.connection.commit()
            return True
        except Error as e:
            print(f"Error: '{e}'")
            return None

    def execute_search_query(self, query, params=None):
        try:
            if params is None:
                self.cursor.execute(query)
            else:
                self.cursor.execute(query, params)
            self.connection.commit()
            return self.cursor.fetchall()
        except Error as e:
            print(f"Error: '{e}'")
            return None

    def insert_data(self, table, columns, values):
        query = f"INSERT INTO {table} ({', '.join(columns)}) VALUES ({', '.join(['%s'] * len(values))})"
        return self.execute_query(query, values)

    def update_data(self, table, columns_values, where_column, where_value):
        set_clause = ', '.join([f"{k}=%s" for k in columns_values.keys()])
        query = f"UPDATE {table} SET {set_clause} WHERE {where_column}"
        values = list(columns_values.values())
        values.append(where_value)
        return self.execute_query(query, values)

    def delete_data(self, table, where_column, where_value):
        query = f"DELETE FROM {table} WHERE {where_column}"
        return self.execute_query(query, where_value)

    def delete_all_data(self, table):
        query = f"DELETE FROM {table}"
        return self.execute_query(query)

    def select_data(self, table, columns="*", where_clause=None, params=None):
        if where_clause:
            query = f"SELECT {columns} FROM {table} WHERE {where_clause}"
        else:
            query = f"SELECT {columns} FROM {table}"
        return self.execute_search_query(query,params)


